"""
MySQL数据库操作，提供连接和查询功能
"""
import mysql.connector
import pandas as pd
from io import StringIO
from fastapi import UploadFile, HTTPException
import config
import models
import uuid

def get_db_connection():
    """创建MySQL数据库连接"""
    try:
        conn = mysql.connector.connect(
            host=config.DB_CONFIG["host"],
            user=config.DB_CONFIG["user"],
            password=config.DB_CONFIG["password"],
            port=config.DB_CONFIG["port"],
            database=config.DB_CONFIG["database"],
            charset='utf8mb4',  # 添加字符集配置
            use_pure=True       # 使用纯Python实现，避免C扩展问题
        )
        return conn
    except mysql.connector.Error as err:
        print(f"MySQL连接错误: {err}")
        raise HTTPException(status_code=500, detail=f"数据库连接错误: {str(err)}")

def create_database_if_not_exists():
    """创建数据库（如果不存在）"""
    try:
        # 连接到MySQL服务器，不指定数据库
        conn = mysql.connector.connect(
            host=config.DB_CONFIG["host"],
            user=config.DB_CONFIG["user"],
            password=config.DB_CONFIG["password"],
            port=config.DB_CONFIG["port"]
        )
        
        cursor = conn.cursor()
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {config.DB_CONFIG['database']}")
        conn.commit()
        cursor.close()
        conn.close()
        return True
    except mysql.connector.Error as err:
        print(f"创建数据库错误: {err}")
        return False

def infer_schema_from_dataframe(df, table_name):
    """从DataFrame推断数据库结构"""
    columns = []
    
    for col_name in df.columns:
        col_type = "VARCHAR(255)"
        description = f"{col_name}字段"
        
        # 基于数据类型推断SQL类型
        if pd.api.types.is_integer_dtype(df[col_name]):
            col_type = "INT"
        elif pd.api.types.is_float_dtype(df[col_name]):
            col_type = "DOUBLE"
        elif pd.api.types.is_bool_dtype(df[col_name]):
            col_type = "BOOLEAN"
        elif pd.api.types.is_datetime64_any_dtype(df[col_name]):
            col_type = "DATETIME"
        
        columns.append({
            "name": col_name,
            "type": col_type,
            "description": description
        })
    
    # 创建表模式
    table = {
        "name": table_name,
        "columns": columns
    }
    
    # 创建数据库模式
    schema = {
        "tables": [table]
    }
    
    return models.DatabaseSchema(**schema)

def get_database_schema():
    """从现有数据库中提取模式"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        
        # 获取所有表
        cursor.execute("SHOW TABLES")
        tables = [row[f"Tables_in_{config.DB_CONFIG['database']}"] for row in cursor.fetchall()]
        
        schema_tables = []
        
        for table_name in tables:
            # 获取表结构
            cursor.execute(f"SHOW FULL COLUMNS FROM {table_name}")
            columns_info = cursor.fetchall()
            
            columns = []
            for col in columns_info:
                col_name = col["Field"]
                col_type = col["Type"]
                col_comment = col["Comment"] or f"{col_name}字段"
                
                # 检查是否是ENUM类型
                enum_values = None
                if col_type.startswith('enum('):
                    # 解析ENUM值
                    enum_str = col_type[5:-1]  # 移除 enum() 
                    enum_list = [v.strip("'\"") for v in enum_str.split(',')]
                    enum_values = {val: val for val in enum_list}
                
                # 简化类型
                if "enum" in col_type.lower():
                    simple_type = "ENUM"
                elif "int" in col_type.lower():
                    simple_type = "INT"
                elif "varchar" in col_type.lower() or "char" in col_type.lower():
                    simple_type = "VARCHAR"
                elif "float" in col_type.lower() or "double" in col_type.lower():
                    simple_type = "DOUBLE"
                elif "date" in col_type.lower() or "time" in col_type.lower():
                    simple_type = "DATETIME"
                elif "text" in col_type.lower():
                    simple_type = "TEXT"
                elif "decimal" in col_type.lower():
                    simple_type = "DECIMAL"
                else:
                    simple_type = "VARCHAR"
                
                columns.append({
                    "name": col_name,
                    "type": simple_type,
                    "description": col_comment,
                    "enum_values": enum_values
                })
            
            schema_tables.append({
                "name": table_name,
                "columns": columns
            })
        
        cursor.close()
        conn.close()
        
        return models.DatabaseSchema(tables=schema_tables)
        
    except Exception as e:
        print(f"从数据库提取模式错误: {e}")
        return None


def init_db():
    """基于模式初始化数据库表"""
    if not config.DB_SCHEMA:
        return False
    
    # 确保数据库存在
    create_database_if_not_exists()
    
    # 连接到指定数据库
    conn = get_db_connection()
    cursor = conn.cursor()
    
    for table in config.DB_SCHEMA.tables:
        # 删除表（如果存在）
        cursor.execute(f"DROP TABLE IF EXISTS {table.name}")
        
        # 创建表
        columns_def = []
        for col in table.columns:
            col_type = "VARCHAR(255)"  # 默认类型
            if col.type.lower() in ["int", "integer"]:
                col_type = "INT"
            elif col.type.lower() in ["float", "real", "double"]:
                col_type = "DOUBLE"
            elif col.type.lower() in ["text", "longtext"]:
                col_type = "TEXT"
            
            columns_def.append(f"{col.name} {col_type}")
        
        create_table_sql = f"CREATE TABLE {table.name} ({', '.join(columns_def)})"
        cursor.execute(create_table_sql)
    
    conn.commit()
    cursor.close()
    conn.close()
    return True

async def import_data_file(file: UploadFile):
    """从CSV/Excel文件导入数据并自动推断结构"""
    try:
        # 读取文件内容
        contents = await file.read()
        
        # 确定文件类型并读入pandas DataFrame
        if file.filename.endswith('.csv'):
            df = pd.read_csv(StringIO(contents.decode('utf-8')))
        elif file.filename.endswith(('.xlsx', '.xls')):
            df = pd.read_excel(contents)
        else:
            raise HTTPException(status_code=400, detail="仅支持CSV或Excel文件")
        
        # 生成临时表名
        table_name = f"{config.TEMP_TABLE_PREFIX}{uuid.uuid4().hex[:8]}"
        
        # 自动推断数据库结构
        config.DB_SCHEMA = infer_schema_from_dataframe(df, table_name)
        
        # 初始化数据库表
        init_db()
        
        # 导入数据到MySQL数据库
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 准备INSERT语句
        columns = ", ".join(df.columns)
        placeholders = ", ".join(["%s"] * len(df.columns))
        insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        
        # 批量插入数据
        values = [tuple(row) for row in df.values]
        cursor.executemany(insert_query, values)
        
        conn.commit()
        records_imported = len(df)
        cursor.close()
        conn.close()
        
        # 设置标志使用导入的数据
        config.USING_IMPORTED_DATA = True
        
        return records_imported
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"导入数据错误: {str(e)}")


def execute_query(sql_query, max_results=100):
    """执行SQL查询并返回结果"""
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)  # 使用字典游标，返回字典结果
    
    try:
        cursor.execute(sql_query)
        
        if cursor.with_rows:
            results = cursor.fetchall()
            
            # 限制结果数量
            if len(results) > max_results:
                results = results[:max_results]
                
            return results, None
        else:
            # 如果是DDL或DML，返回空结果集
            return [], None
            
    except mysql.connector.Error as e:
        return [], str(e)
    finally:
        cursor.close()
        conn.close()
